package com.consume.bhy.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.consume.bhy.entity.ConsumeEntity;
import com.consume.bhy.entity.PageEntity;
import com.consume.bhy.entity.ShowEntity;
import com.consume.bhy.jdbc.JDBC_Util;

public class ConsumeService {
	
	public PageEntity<List<ShowEntity>> searchConsume(String searchContent, String searchChoose, String pageNo) throws SQLException {
		
		Connection conn = JDBC_Util.getConnection();
	
		PageEntity<List<ShowEntity>> page = new PageEntity<List<ShowEntity>>();
		if(pageNo==null || pageNo=="") {
			pageNo="1";
		}
		page.setPageNo(Integer.parseInt(pageNo));
		
		StringBuffer sql = new StringBuffer("select i.consume_id id,i.consume_name name,i.consume_price price,i.consume_count count,i.consume_date consumeDate,t.consume_type_name typeName,i.remark infoRemark,t.remark typeRemark  from consume_info i,consume_type t where i.consume_type_id=t.consume_type_id ");
	
		List<Object> prepareList = new ArrayList<Object>();
		
		if(searchContent != null && searchContent!="") {
			sql.append(" and i.consume_name like ?");
			prepareList.add("%"+searchContent+"%");
		}
		if(!"0".equals(searchChoose) && searchChoose != null) {
			
				sql.append(" and t.consume_type_name=?");
				prepareList.add(searchChoose);
		}
		

		
		PreparedStatement preparedPage = conn.prepareStatement(sql.toString());
	
		
		for (int i=0; i < prepareList.size(); i++) {
			
			preparedPage.setObject(i+1, prepareList.get(i));
		}

		ResultSet resultPage = preparedPage.executeQuery();
		int totalRecords = 0;
		
		while(resultPage.next()) {
			totalRecords++;
		}
		JDBC_Util.clossJDBC(preparedPage, resultPage);

		sql.append(" order by consume_date DESC limit ?, ?");
		
		prepareList.add(page.getPageSize()*(page.getPageNo()-1));
		prepareList.add(page.getPageSize());
		
		
		PreparedStatement prepared = conn.prepareStatement(sql.toString());
	
		
		for (int i=0; i < prepareList.size(); i++) {
			
			prepared.setObject(i+1, prepareList.get(i));
		}

		ResultSet result = prepared.executeQuery();
		
		List<ShowEntity> list = new ArrayList<ShowEntity>();
		
		
		while(result.next()) {
			ShowEntity cla = new ShowEntity();
			cla.setId(result.getInt("id"));
			cla.setName(result.getString("name"));
			cla.setPrice(result.getDouble("price"));
			cla.setCount(result.getInt("count"));
			
//			cla.setMoney(result.getDouble("money"));
			cla.setTypeName(result.getString("typeName"));
			cla.setConsumeDate(result.getTimestamp("consumeDate"));

			list.add(cla);
			
			
		}
		
		page.setData(list);
		
		List<String> classList = new ArrayList<String>();
		String sql3 = "select consume_type_name from consume_type";
		PreparedStatement preparedStatement3 = conn.prepareStatement(sql3);
		ResultSet result3 = preparedStatement3.executeQuery();
		while(result3.next()) {
			classList.add(result3.getString("consume_type_name"));
		}
		JDBC_Util.clossJDBC(preparedStatement3, result3);
		
		page.setClassList(classList);
		
	
		page.setTotalRecords(totalRecords);
		
		return page;

	}
	

	public boolean addConsume(ConsumeEntity consume) throws SQLException {
		
		Connection conn = JDBC_Util.getConnection();
		String sql = "insert into consume_info values(null,?,?,?,?,?,?)";
		
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setString(1, consume.getName());
		preparedStatement.setDouble(2,consume.getPrice());
		
		preparedStatement.setInt(3, consume.getCount());
		
//		SimpleDateFormat sd1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//		String d1 = sd1.format(consume.getDate());

		preparedStatement.setObject(4,consume.getDate());
		preparedStatement.setInt(5, consume.getTypeId());
		
		preparedStatement.setString(6, consume.getRemark());
		int result = preparedStatement.executeUpdate();
	
		return result==1;
		
	}


	public PageEntity<List<ShowEntity>> showAddConsume() throws SQLException {
		
		
		Connection conn = JDBC_Util.getConnection();
		List<String> classList = new ArrayList<String>();
		
		String sql3 = "select consume_type_name from consume_type";
		PreparedStatement preparedStatement3 = conn.prepareStatement(sql3);
		ResultSet result3 = preparedStatement3.executeQuery();
		while(result3.next()) {
			classList.add(result3.getString("consume_type_name"));
		}
		JDBC_Util.clossJDBC(conn,preparedStatement3, result3);
		
		PageEntity<List<ShowEntity>> page = new PageEntity<List<ShowEntity>>();
		
		page.setClassList(classList);
		return page;
		
	}

	public boolean deleteConsume(int id) throws SQLException {

		Connection conn = JDBC_Util.getConnection();
		
			String sql2 = "delete from consume_info where consume_id=?";
			
			PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
			preparedStatement2.setInt(1, id);
			int result2 = preparedStatement2.executeUpdate();
			
			return result2==1;
		}


	public ConsumeEntity updateConsume(String id) throws SQLException {
		Connection conn = JDBC_Util.getConnection();
		String sql = "select * from consume_info where consume_id=?";
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setInt(1, Integer.parseInt(id));
		ResultSet result = preparedStatement.executeQuery();
	
		if(result.next()) {
			ConsumeEntity cla = new ConsumeEntity();
	
			cla.setId(result.getInt("consume_id"));
			cla.setName(result.getString("consume_name"));
			cla.setPrice(result.getDouble("consume_price"));
			cla.setCount(result.getInt("consume_count"));
			cla.setDate(result.getTimestamp("consume_date"));
			cla.setTypeId(result.getInt("consume_type_id"));
			cla.setRemark(result.getString("remark"));
			return cla;
		}
		
		return null;
	}
	
	public List<String> typeNameList() throws SQLException {
		
		Connection conn = JDBC_Util.getConnection();
		String sql = "select consume_type_name from consume_type";
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		ResultSet result = preparedStatement.executeQuery();
		
		List<String> list = new ArrayList<String>();
		
		while(result.next()) {
	
			list.add(result.getString("consume_type_name"));
		}
		
		return list;

	}

	public boolean updateConsumeInfo(ConsumeEntity consume) throws SQLException {
		
		Connection conn = JDBC_Util.getConnection();
		String sql = "update consume_info set consume_name=?,consume_price=?,consume_count=?,consume_date=?,consume_type_id=?,remark=? where consume_id=?";
		
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		
		preparedStatement.setString(1, consume.getName());
		preparedStatement.setDouble(2, consume.getPrice());
		preparedStatement.setInt(3, consume.getCount());
		preparedStatement.setObject(4, consume.getDate());
		preparedStatement.setInt(5, consume.getTypeId());
		preparedStatement.setString(6, consume.getRemark());
		preparedStatement.setInt(7, consume.getId());
	
		int result = preparedStatement.executeUpdate();
	
		return result==1;
		
	}


	

}
